


--use PolePosition

-- // checksum failed custom error
--exec sp_addmessage @msgnum = 505050, @severity = 16, @msgtext = N'%s(%s) checksum compare failed', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // not found custom error
--exec sp_addmessage @msgnum = 505060, @severity = 16, @msgtext = N'%s(%s) does not exist', @replace = 'REPLACE', @lang = 'us_english'
GO

-- // unit test custom error
--exec sp_addmessage @msgnum = 505070, @severity = 16, @msgtext = N'%s Test Failed. Expected< %s > Actual< %s > %s', @replace = 'REPLACE', @lang = 'us_english'
GO



--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'v_tb_CentralOffices') and OBJECTPROPERTY(id, N'IsProcedure') = 0)
drop view v_tb_CentralOffices
GO

create view v_tb_CentralOffices as
	/* 	Initial Generated Date:  Sunday, April 12, 2009 
		Purpose: Selected record(s) from the table; this view is the source for all procedures' select statements
	*/
	
select
	 [CentralOfficeId] as [CentralOfficeId]
	,[Name] as [Name]
	,[Latitude] as [Latitude]
	,[Longitude] as [Longitude]
	,[Address] as [Address]
	,[UtilityFk] as [UtilityFk]
	-- customization may be required for FriendlyName
	,Left(CONVERT(varchar(255), [Name]), 255) as [FriendlyName]
from
	tb_CentralOffices
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_CentralOffice_Test') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_CentralOffice_Test
GO


create proc up_CentralOffice_Test
	@CentralOfficeId int
as
	/* 	Initial Generated Date:  Sunday, April 12, 2009 
		Purpose: Test if a record exists, using the primary key
	*/

SET NOCOUNT ON	
	
if exists(
	select [CentralOfficeId] 
	from [tb_CentralOffices] source
	where [source].[CentralOfficeId] = @CentralOfficeId)
begin
	return(1)
end
else
begin
	return(0)
end
GO






--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_CentralOffice_Select') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_CentralOffice_Select
GO

create proc up_CentralOffice_Select
	 @CentralOfficeId int = null 
	,@Name nvarchar(100) = null 
	,@Latitude float = null 
	,@Longitude float = null 
	,@Address nvarchar(250) = null 
	,@UtilityFk int = null 
as
	/* 	Initial Generated Date:  Sunday, April 12, 2009 
		Purpose: Selected record(s) based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select * from v_tb_CentralOffices
where
		IsNull([v_tb_CentralOffices].[CentralOfficeId], -1) = Coalesce(@CentralOfficeId, [v_tb_CentralOffices].[CentralOfficeId], -1)
	AND	IsNull([v_tb_CentralOffices].[Name], '') = Coalesce(@Name, [v_tb_CentralOffices].[Name], '')
	AND	IsNull([v_tb_CentralOffices].[Latitude], -1) = Coalesce(@Latitude, [v_tb_CentralOffices].[Latitude], -1)
	AND	IsNull([v_tb_CentralOffices].[Longitude], -1) = Coalesce(@Longitude, [v_tb_CentralOffices].[Longitude], -1)
	AND	IsNull([v_tb_CentralOffices].[Address], '') = Coalesce(@Address, [v_tb_CentralOffices].[Address], '')
	AND	IsNull([v_tb_CentralOffices].[UtilityFk], -1) = Coalesce(@UtilityFk, [v_tb_CentralOffices].[UtilityFk], -1)
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_CentralOffice_Select_Top1') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_CentralOffice_Select_Top1
GO

create proc up_CentralOffice_Select_Top1
	 @CentralOfficeId int = null 
	,@Name nvarchar(100) = null 
	,@Latitude float = null 
	,@Longitude float = null 
	,@Address nvarchar(250) = null 
	,@UtilityFk int = null 
as
	/* 	Initial Generated Date:  Sunday, April 12, 2009 
		Purpose: Selected top 1 record based on the passed arguments; no arguments returns all
	*/

SET NOCOUNT ON	

select TOP 1 * from v_tb_CentralOffices
where
		IsNull([v_tb_CentralOffices].[CentralOfficeId], -1) = Coalesce(@CentralOfficeId, [v_tb_CentralOffices].[CentralOfficeId], -1)
	AND	IsNull([v_tb_CentralOffices].[Name], '') = Coalesce(@Name, [v_tb_CentralOffices].[Name], '')
	AND	IsNull([v_tb_CentralOffices].[Latitude], -1) = Coalesce(@Latitude, [v_tb_CentralOffices].[Latitude], -1)
	AND	IsNull([v_tb_CentralOffices].[Longitude], -1) = Coalesce(@Longitude, [v_tb_CentralOffices].[Longitude], -1)
	AND	IsNull([v_tb_CentralOffices].[Address], '') = Coalesce(@Address, [v_tb_CentralOffices].[Address], '')
	AND	IsNull([v_tb_CentralOffices].[UtilityFk], -1) = Coalesce(@UtilityFk, [v_tb_CentralOffices].[UtilityFk], -1)
GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_CentralOffice_Insert') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_CentralOffice_Insert
GO

create proc up_CentralOffice_Insert
	 @CentralOfficeId int =null OUTPUT  
	,@Name nvarchar(100)  
	,@Latitude float  
	,@Longitude float  
	,@Address nvarchar(250) = null  
	,@UtilityFk int = null  
as
	/* 	Initial Generated Date:  Sunday, April 12, 2009 
		Purpose: Inserts a new record; missing arguments are set as their default (typically null)
	*/

SET NOCOUNT ON	
--int

insert tb_CentralOffices 
	(
	
	 [Name] 
	,[Latitude] 
	,[Longitude] 
	,[Address] 
	,[UtilityFk] 
	)
	values
	(
	
	 @Name 
	,@Latitude 
	,@Longitude 
	,@Address 
	,@UtilityFk 
	)
set @CentralOfficeId = scope_identity()
select * from v_tb_CentralOffices where CentralOfficeId = @CentralOfficeId

GO




--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_CentralOffice_Update') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_CentralOffice_Update
GO


create proc up_CentralOffice_Update
	 @CentralOfficeId int  
	,@Name nvarchar(100) = null  
	,@Latitude float = null  
	,@Longitude float = null  
	,@Address nvarchar(250) = null  
	,@UtilityFk int = null  
	,@OriginalCentralOfficeId int  = null --Original PrimaryKey
	
as
	/* 	Initial Generated Date:  Sunday, April 12, 2009 
		Purpose: Updates an existing record, verifying exist; missing arguments persist original record value
		NOTE: this version of the update allows up to write nulls to the table.
	*/

SET NOCOUNT ON	

if @OriginalCentralOfficeId is null
	set @OriginalCentralOfficeId = @CentralOfficeId

-- test
declare @Result bit
execute @Result = up_CentralOffice_Test @OriginalCentralOfficeId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @CentralOfficeId)
	raiserror(505060, 16, 1, 'CentralOffice', @x)
	return
end

-- proceed
update tb_CentralOffices 
set

	 [Name] = IsNull(@Name, [source].[Name])
	,[Latitude] = IsNull(@Latitude, [source].[Latitude])
	,[Longitude] = IsNull(@Longitude, [source].[Longitude])
	,[Address] =  @Address
	,[UtilityFk] =  @UtilityFk
	
from tb_CentralOffices source
where
	[source].[CentralOfficeId] = @OriginalCentralOfficeId


select * from v_tb_CentralOffices where CentralOfficeId = @CentralOfficeId
GO





--use PolePosition

if exists (select * from dbo.sysobjects where id = object_id(N'up_CentralOffice_Delete') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure up_CentralOffice_Delete
GO


create proc up_CentralOffice_Delete
	@CentralOfficeId int
as
	/* 	Initial Generated Date:  Sunday, April 12, 2009 
		Purpose: Deletes an existing record, verifying exist
	*/

SET NOCOUNT ON	

-- test
declare @Result bit
execute @Result = up_CentralOffice_Test @CentralOfficeId
if (not @Result = 1)
begin
	Declare @x varchar(100)
	Set @x = Convert(Varchar(100), @CentralOfficeId)
	raiserror(505060, 16, 1, 'CentralOffice', @x)
	return
end

-- proceed
delete from tb_CentralOffices 
where [CentralOfficeId] = @CentralOfficeId

GO


